1. Field of the Invention
The invention relates to a technique for a log capture program in a database management system. In particular, this technique is for database replication using a log capture program that publishes transactions to multiple targets and handles unavailable targets by separating the publishing of subscriptions from a mainline log reader and subsequently transferring the publishing back to the mainline log reader. This technique is particularly, though not exclusively, suited for use with a database management system.
2. Description of Related Art
Database management systems allow large volumes of data to be stored and accessed efficiently and conveniently in a computer system. In a database management system, data is stored in database tables which effectively organize the data into rows and columns. A database engine responds to user commands to store and access the data.
In FIG. 1, a database table 20 having rows 22 and columns 24 is shown. A row 22 has one or more columns 24. A row 22 is also referred to as a database record.
Data replication refers to maintaining duplicate sets of data. A source database, or portions thereof, may be replicated on one or more target databases. Some users mirror their data in a target database at a single location, while other users replicate all or portions of their data in target databases at different locations. By replicating data in target databases, some users can continue to access their target databases even though the source database or other target databases may be unavailable. In addition, having replicated data on multiple target computers reduces the processing load at the source computer because a user can access the data on a target computer.
The database management system maintains a log to record transactions for recovery. This log is referred to as the source database log. The log contains records for each change to the database and for commit and rollback. The database uses a write-ahead logging protocol (WAL) under which changes made by applications are first logged in the log before being written to disk. In the case of a crash, the database management system will replay the log records to restore the database to a consistent state. For each change log record, there is a matching rollback or commit record. The commit record marks the end of a series of change records in the log for a transaction that was successfully committed to the database. The rollback record marks the end of a transaction that was not completed (aborted) by the user.
In one replication system, a registration is specified to indicate which changes from the log are to be captured into a staging area, and then a subscription indicates which changes will be extracted from the staging area and sent to a target database. In one embodiment, the subscriptions are stored in one or more database tables in the source database. Each target database is associated with a queue to which the subscribed changes for that target database are written. The subscription identifies a database table and the queue to which the subscribed changes to that table should be written. The subscription allows a user to subscribe to all or a subset of the columns of a table. For example, one user may want to replicate only the name and address when the address is changed, rather than an entire employee record on one target database. The subscription may also specify a predicate for filtering changes, such as replicate only employees “where state=‘CA’”. In an exemplary subscription below, when the table ORDERS is updated, the columns called custid and amount are sent to the queue called PURCHASESQ when the state is equal to “CA.”
subscription: S1
table name: ORDERS
queue: PURCHASESQ
columns: custid, amount
predicate: “Where state=‘CA’”
The replication technique may impact the performance of the database management system and the applications that access the database. For example, some replication techniques are based on pre-defined triggers. The triggers are fired when applications update the database. In another technique, the applications use a two-phase commit to save the changes to both the source and replica database in the same transaction. These techniques introduce a fixed overhead for each transaction, degrade the overall throughput, and are not scalable to multiple targets.
Another technique, referred to as log capture based replication, has much less impact than the aforementioned techniques. In log capture based replication, a capture program, also referred to as a log reader, reads the source database log and sends transactions to a staging area for a target database in accordance with the subscription. The capture program operates asynchronously with respect to applications that may be updating the database tables. One advantage to log capture based replication is that changes to the database can be captured and replicated without impacting the applications that are using the database. This is particularly true when the log can be captured using dedicated processors and input/output subsystems, such as when the log is physically mirrored on separate hardware.
In one technique, the staging area may be implemented as a set of relational database tables. Alternately, the staging area may be implemented as a queue. Directly writing the captured changes to a queue is faster than using database tables. In some replication schemes, each target database is associated with a queue and a reader, typically referred to as an Apply program. The Apply program reads the transactions from the queue and applies the transactions to the tables of the target database.
A transaction is a sequence of updates, inserts and deletes followed by a commit. In the context of a table, the term “change” refers to an update, an insert or a delete. Given that a user subscribes to changes to tables, the transactions published on the queue may be a subset of all the transactions performed in the original system. Furthermore, the transactions published on the queue may contain a subset of all the changes making up a transaction. In addition, a particular change for a table for which there is a subscription may not be published to a queue depending on the subscription predicates. For example, assume that a database has three tables—ORDERS, ACCOUNT and BILLING. The database has two subscriptions, one to table ORDERS and another to table ACCOUNT. The following transaction is performed against the database:
INSERT into ORDERS (custid, amount) values (5000, 19.99);
UPDATE ACCOUNT set balance=19.99 where custid=5000;
INSERT into BILLING values (5000, 19.99, itemno);
COMMIT;
The capture program will publish only a portion of the above transaction. The portion of the transaction published by the capture program will contain the changes, the insert and the update, to the ORDERS and ACCOUNT tables, but not the insert to the BILLING table.
A problem arises when a queue becomes unavailable. For example, a queue may fail, the queue or its associated target computer may be taken offline by an administrator, or the queue may fill up because its queue reader, its Apply program, is not retrieving transactions fast enough. One solution is to stop the capture program until the unavailable queue becomes available. However, readers for other queues may continue to be available and operational. Stopping the capture program penalizes those readers and target databases that are still available. If the available queue readers are maintaining target database replicas, those target database replicas would be lagging further behind the source database where changes may still be taking place. One unavailable queue should not penalize all target databases. Therefore, a technique is needed to allow the capture program to continue publishing transactions to available queues when one or more queues becomes unavailable and to resume publishing when a queue becomes available.